﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using OestsCommon.Model;
using OestsCommon.TcpNet;
using Utils;

namespace OestsDataBase
{
    public static class TheoryPaperDAL
    {
        private static Subject ReadSubject(int paperId, DataRow dr)
        {
            Subject sub = new Subject();
            SubjectType subType = (SubjectType)Enum.Parse(typeof(SubjectType), dr["subject_type"].ToString());
            int id = Int32.Parse(dr["subject_id"].ToString());
            if (subType == SubjectType.Choice)
            {
                sub = ChoiceDAL.GetChoiceById(id);
            }
            else
            {
                sub = TrueOrFalseDAL.GetTrueOrFalseById(id);
            }
            if (sub != null)
                sub.SetScore = Int32.Parse(dr["subject_score"].ToString());
            else
                DelSubjectInTheoryPaper(paperId, id, subType);//删除不存在的试题
            return sub;
        }
        public static TheoryPaper GetTheoryPaper(int paperId)
        {
            string strSql = "select * from T_TheoryPapers where paper_id = " + paperId;
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null || dt.Rows.Count == 0) { return null; }
            DataRow dr = dt.Rows[0];
            TheoryPaper tp = ReadData(dr);
            return tp;
        }

        public static List<Subject> GetSubjectListByPapaerId(int paperId)
        {
            string strSql = "select * from T_TheoryPaper_" + paperId;
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null) { return null; }

            List<Subject> list = new List<Subject>();

            foreach (DataRow dr in dt.Rows)
            {
                Subject sub = ReadSubject(paperId, dr);
                if (sub != null)
                    list.Add(sub);
            }
            return list;
        }

        public static List<int> GetStudentInTheoryPaper(int paperId)
        {
            string strSql = "select student_ids from T_TheoryPapers where paper_id = " + paperId;
            object obj = SqlHelper.ExecuteScalar(strSql);
            if (obj == null) return null;
            string stuentIdsStr = "";
            if (obj.Equals(DBNull.Value))
            {
                return new List<int>();
            }

            stuentIdsStr = (string)obj;
            string[] ids = stuentIdsStr.Split(',');
            List<int> data = stuentIdsStr.ToNList();
            return data;
        }

        public static bool ModifyStudents(int paperId, string students)
        {
            string strSql = string.Format("update T_TheoryPapers set student_ids='{0}' where paper_id = @id", students);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", paperId));
            if (n != 0)
            {
                return true;
            }
            return false;
        }

        public static List<int> GetPaperIdListByTeacherId(int teacherId)
        {
            List<int> list = new List<int>();
            string strSql = "select paper_id from T_TheoryPapers where teacher_id = " + teacherId;
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null) { return null; }
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(Int32.Parse(dr["paper_id"].ToString()));
            }
            return list;
        }

        /// <summary>
        /// 获取某个教师权限下的试卷
        /// </summary>
        /// <param name="teacherid"></param>
        /// <returns></returns>
        public static List<TheoryPaper> GetTheoryPaperListByTeacher(int teacherid)
        {
            List<TheoryPaper> list = new List<TheoryPaper>();
            string strSql = "select * from T_TheoryPapers where teacher_id = " + teacherid;
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null) { return null; }
            foreach (DataRow dr in dt.Rows)
            {
                TheoryPaper tp = ReadData(dr);
                list.Add(tp);
            }
            return list;
        }

        private static TheoryPaper ReadData(DataRow dr)
        {
            TheoryPaper tp = new TheoryPaper();
            tp.Id = Int32.Parse(dr["paper_id"].ToString());
            tp.Name = dr["paper_name"].ToString();
            tp.ProjectId = Int32.Parse(dr["projectId"].ToString());
            tp.Time = Int32.Parse(dr["paper_time"].ToString());
            if (!dr["student_ids"].Equals(DBNull.Value))
            {
                string stuentIdsStr = dr["student_ids"].ToString();
                tp.StudentList = stuentIdsStr.ToNList();
            }
            tp.TeacherId = Int32.Parse(dr["teacher_id"].ToString());
            tp.SubjectList = GetSubjectListByPapaerId(tp.Id);
            return tp;
        }


        public static List<TheoryPaper> GetData()
        {
            string strSql = "select * from T_TheoryPapers";
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null) { return null; }
            List<TheoryPaper> list = new List<TheoryPaper>();
            foreach (DataRow dr in dt.Rows)
            {
                TheoryPaper tp = ReadData(dr);
                list.Add(tp);
            }
            return list;
        }


        /// <summary>
        /// 添加一张试卷
        /// </summary>
        /// <param name="name">试卷名称</param>
        /// <param name="time">试卷时间</param>
        /// <param name="teacherId">教师id</param>
        /// <returns></returns>
        public static int AddTheoryPaper(string paper_name, int post_id, int paper_time, int teacherId)
        {
            if (paper_name == "") return -1;
            string strSql = string.Format(@"insert into T_TheoryPapers (paper_name,projectId,paper_time,teacher_id)values('{0}',{1},{2},{3});SELECT @@Identity", paper_name, post_id, paper_time, teacherId);
            object obj = SqlHelper.ExecuteScalar(strSql);

            int nID = Int32.Parse(obj.ToString());
            //创建一张试卷表
            CreatTheoryPaperTable(nID);
            return nID;
        }

        /// <summary>
        /// 创建实训组的表
        /// </summary>
        /// <param name="id"></param>
        private static void CreatTheoryPaperTable(int id)
        {
            string sqlCheck = string.Format("if object_id('{0}') is not null select 1 else select 0", "T_TheoryPaper_" + id);
            if ((int)SqlHelper.ExecuteScalar(sqlCheck) == 0)
            {
                string sqlCreate = @"CREATE TABLE T_TheoryPaper_" + id + @" (subject_id bigint ,subject_type varchar(20), subject_score int,primary key(subject_id,subject_type))";
                SqlHelper.ExecuteNonQuery(sqlCreate);
            }
            else
            {
                //清空已有数据，并且将自增自段恢复从1开始计数 
                string sql = "truncate table T_TheoryPaper_" + id;
                SqlHelper.ExecuteNonQuery(sql);
            }
        }

        /// <summary>
        /// 修改 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool ModifyTheoryPaperName(int id, string name = null)
        {
            string strSql = string.Format("update T_TheoryPapers set paper_name='{0}' where paper_id = @id", name);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
            if (n != 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 删除实训组  
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DelTheoryPaper(int id)
        {
            string strSql = string.Format("delete from  T_TheoryPapers  where paper_id = @id");
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
            if (n != 0)
            {
                // 删除对应表
                string sqlCheck = string.Format("if object_id('{0}') is not null select 1 else select 0", "T_TheoryPaper_" + id);
                if ((int)SqlHelper.ExecuteScalar(sqlCheck) == 1)
                {
                    string sqlCreate = @"drop TABLE T_TheoryPaper_" + id;
                    SqlHelper.ExecuteNonQuery(sqlCreate);
                }
                return true;
            }
            return false;
        }


        /// <summary>
        /// 修改 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool ModifyExamInfo(int id, string examName, int time)
        {

            string strSql = string.Format("update T_TheoryPapers set paper_name='{0}',paper_time={1} where paper_id = @id", examName, time);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
            if (n != 0)
            {
                return true;
            }
            return false;
        }

        public static bool ModifyTheoryPaperTime(int id, int time)
        {
            string strSql = string.Format("update T_TheoryPapers set paper_time={0} where paper_id = @id", time);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
            if (n != 0)
            {

                return true;
            }
            return false;
        }

        /// <summary>
        /// 添加试题到试卷
        /// </summary>
        /// <param name="paperId"></param>
        /// <param name="subjectId"></param>
        /// <param name="type"></param>
        /// <param name="score"></param>
        public static void AddSubjectToTheoryPaper(int paperId, int subjectId, SubjectType type, int score = 0)
        {
            string strSql = string.Format(@"insert into T_TheoryPaper_" + paperId + " (subject_id,subject_type,subject_score)values({0},'{1}',{2})", subjectId, type.ToString(), score);
            SqlHelper.ExecuteScalar(strSql);
        }

        public static bool ModifyExamScore(int paperId, int subId, SubjectType type, int score)
        {
            string strSql = string.Format("update T_TheoryPaper_" + paperId + " set subject_score={0} where subject_id = @id and subject_type=@type", score);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", subId), new SqlParameter("@type", type.ToString()));
            if (n != 0)
            {
                return true;
            }
            return false;
        }

        public static bool DelSubjectInTheoryPaper(int paperId, int subId, SubjectType type)
        {
            string strSql = string.Format("delete from  T_TheoryPaper_" + paperId + "  where subject_id = @id and subject_type = @type");
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", subId), new SqlParameter("@type", type.ToString()));
            if (n != 0)
            {
                return true;
            }
            return false;
        }

    }
}
